# !/usr/bin/env python3
# -*- coding: utf-8 -*-

# @Time    : 2020/2/18 11:42
# @File    : SSHForwarderUtil.py
# @Date    : 2020/2/18
# @Author  : Yuwenjun
# @Desc    : SSH跳板连接到数据库或者本地远程执行


import pymysql
import paramiko
import traceback

import redis
from sshtunnel import SSHTunnelForwarder


# SSH服务器配置
HOST_NAME = '39.97.250.105'
USER_NAME = 'root'
PASSWORD = ''
PORT = 22

# MySQL数据库配置
# IP配置
DB_HOST, TEST_DB_HOST = '192.168.0.36', '192.168.0.38'
# 数据库用户名
DB_USER, TEST_DB_USER = 'root', 'root'
# 数据库密码
DB_USER_PASSWORD, TEST_USER_PASSWORD = '123456', ''
# 需要备份的数据库
DB_NAME, TEST_DB_NAME = 'will_art', ''
# 数据库端口
DB_PORT, TEST_DB_PORT = 3305, 3307

# MongoDB配置
# 数据库IP
MONGO_HOST, TEST_MONGO_HOST = '192.168.0.36', ''
# 数据库用户名
MONGO_USER, TEST_MONGO_USER = 'tasks', 'tasks'
# 数据库密码
MONGO_USER_PASSWORD, TEST_MONGO_USER_PASSWORD = 'admin', ''
# 需要导出和导入的数据库名
MONGO_DB_NAME, TEST_MONGO_DB_NAME = 'tasks', ''
# 数据库端口
MONGO_DB_PORT, TEST_MONGO_DB_PORT = 27015, 27016

# redis配置
# 数据库IP
REDIS_HOST, TEST_REDIS_HOST = '192.168.0.37', ''
# 数据库用户名
REDIS_USER, TEST_REDIS_USER = 'tasks', 'tasks'
# 数据库密码
REDIS_USER_PASSWORD, TEST_REDIS_USER_PASSWORD = '', ''
# 需要导出和导入的数据库名
REDIS_DB_NAME, TEST_REDIS_DB_NAME = '1', ''
# 数据库端口
REDIS_DB_PORT, TEST_REDIS_DB_PORT = 6379, 6378


def instance_ssh_server(db_hostname, db_port):
    server = SSHTunnelForwarder((HOST_NAME, PORT), ssh_password=PASSWORD, ssh_username=USER_NAME,
                                remote_bind_address=(db_hostname, db_port))
    return server


def execute_shell(cmd):
    paramiko.util.log_to_file('paramiko.log')
    s = paramiko.SSHClient()
    s.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    s.connect(hostname=HOST_NAME, username=USER_NAME, password=PASSWORD, port=PORT)
    stdin, stdout, stderr = s.exec_command(cmd)
    print(stdout.read())
    s.close()


def execute_sql(sql):
    try:
        server = instance_ssh_server(DB_HOST, DB_PORT)
        server.start()
        db = pymysql.connect(host='127.0.0.1',
                             port=server.local_bind_port,
                             user=DB_USER,
                             passwd=DB_USER_PASSWORD,
                             db=DB_NAME)
        cursor = db.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        print(data)

        db.close()
        server.close()
        return data
    except:
        traceback.print_exc()
        server.close()

def redis_delete(key):
    try:
        server = instance_ssh_server(REDIS_HOST, REDIS_DB_PORT)
        server.start()
        # r = redis.Redis(host='localhost', port=server.local_bind_port, decode_responses=True, db=2, password="m1i2m3a02")
        r = redis.Redis(host='127.0.0.1', port=server.local_bind_port, decode_responses=True, db=1)
        # db = pymysql.connect(host='127.0.0.1',
        #                      port=server.local_bind_port,
        #                      user=rE_USER,
        #                      passwd=DB_USER_PASSWORD,
        #                      db=DB_NAME)
        r.delete(key)
    except:
        traceback.print_exc()
        server.close()


if __name__ == '__main__':
    instance_ssh_server(DB_HOST, DB_PORT)